#!/usr/bin/perl -w
use strict;
use DBI;
######################
##  Jason Weirather 2010
##  This one is officially awesome now.
##  It scans regular expression accross a genome and remembers the results
##  You can follow it up by accessing the database with the parameters you 
##  searched with to retreive all your results.  I leave this alone
##  for building the tables.  You have to write another to retrieve.
##  it requires the zebraphish database to be set up properly.
##  it takes as input the genome name and regular expression you are using
##  it doesn't output anything important and it updates the database.
####################


if(scalar(@ARGV) < 1) { die "use: echo /regularexpression/ | ./locate_matches species_genome\n"; }
my $species = shift @ARGV;
chomp(my $regex = <STDIN>);
if(!$regex) { die "no input\n"; }
if($regex =~ /^\/(.*)\/$/) { $regex = $1; }
if(length($regex) > 1000) { die "regular expression is too long!\n"; }
my $username = "x";
# we REALLY need to sterilize this before we put it on the net though or limit the access to the database
# sterilize the species field too
$regex = "$regex";
my $count = 0;


### get a list of chromosome names
my $zdatabase = "zebraphish";
my $platform = "mysql";
my $user = $username;
my $zdbh = DBI->connect("dbi:$platform:$zdatabase",$user,'');
my $zquery = "SELECT db_id, completed FROM regex_scan WHERE species = '$species' AND regex = '$regex' LIMIT 1";
my $zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
$zst->execute() or die "The execution of query field: $DBI::errstr";
my $matches = 0;
my $completed = 0;
my $regex_id = 0;
while(my $row = $zst->fetchrow_hashref()) {
  $matches++;
  $completed = $row->{'completed'};
  $regex_id = $row->{'db_id'};
}
if($matches > 0) {
  print "regular expression has been used\n";
  if($completed == 1) { 
    print "you finished already!\nfinished_locating_matches\n";
    exit;
  }
} else {
  $zquery = "INSERT INTO regex_scan (regex,species,completed) VALUES ('$regex','$species',0)";
  $zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
  print "make a new regex entry\n$zquery\n";
  $zst->execute() or die "The execution of query field: $DBI::errstr";
  #get the $regex_id now that we have an entry added  
  $zquery = "SELECT db_id, completed FROM regex_scan WHERE species = '$species' AND regex = '$regex' LIMIT 1";
  $zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
  $zst->execute() or die "The execution of query field: $DBI::errstr";
  while(my $row = $zst->fetchrow_hashref()) {
    $regex_id = $row->{'db_id'};
  }
}

my $database = "compressed_genomes";
my $dbh = DBI->connect("dbi:$platform:$database",$user,'');
my $query = "SELECT chromosome FROM $species ORDER BY chromosome"; #be careful that this one doesn't change when you modify things
my $st = $dbh->prepare($query) or die "preparing query failed: $DBI::errstr";
$st->execute() or die "The execution of query field: $DBI::errstr";
my @chromosomes;
while(my $row = $st->fetchrow_hashref()) {
  #see all the genes it is upstream of.
  push @chromosomes, $row->{'chromosome'};
  print $row->{'chromosome'} . "\n";
}

#connect up for the per chromosome sequence queries
$query = "SET GLOBAL MAX_ALLOWED_PACKET = 1000000000";
$st = $dbh->prepare($query) or die "preparing query failed: $DBI::errstr";
$st->execute() or die "The execution of query field: $DBI::errstr";
foreach my $chromosome (@chromosomes) {
  #see if the chromosome has been run already
  my $zquery = "SELECT db_id, completed FROM chromosome_scan WHERE genome = '$species' AND regex_id = $regex_id AND chromosome = '$chromosome' LIMIT 1";
  my $zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
  $zst->execute() or die "The execution of query field: $DBI::errstr";
  my $entries = 0;
  my $chromosome_scan_id = 0;
  my $completed_chromosome = 0;
  while(my $row = $zst->fetchrow_hashref()) {
    $entries++;
    $chromosome_scan_id = $row->{'db_id'};
    $completed_chromosome = $row->{'completed'};
  }
  if($completed_chromosome != 1) { # we are not finished yet
    if($entries > 0) {
      # we have at least started it before
      #die "delete both the chromosome scan entry and the hits\n";
      $zquery = "DELETE FROM chromosome_scan WHERE db_id = $chromosome_scan_id";
      $zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
      print "Deleting chromosome scan entry\n$zquery\n";
      $zst->execute() or die "The execution of query field: $DBI::errstr";
      $zquery = "DELETE FROM hits WHERE chromosome_scan_id = $chromosome_scan_id";
      $zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
      print "Deleting hits to prevent redunancy\n$zquery\n";
      $zst->execute() or die "The execution of query field: $DBI::errstr";
    }

    $zquery = "INSERT INTO chromosome_scan (regex_id,chromosome,completed,genome) VALUES ($regex_id,'$chromosome',0,'$species')";
    $zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
    print "make a new chromosome scan entry\n$zquery\n";
    $zst->execute() or die "The execution of query field: $DBI::errstr";
    #get the $regex_id now that we have an entry added  
    $zquery = "SELECT db_id, completed FROM chromosome_scan WHERE chromosome = '$chromosome' AND regex_id = $regex_id AND genome = '$species' LIMIT 1";
    $zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
    $zst->execute() or die "The execution of query field: $DBI::errstr";
    while(my $row = $zst->fetchrow_hashref()) {
      $chromosome_scan_id = $row->{'db_id'};
    }      
  
    #go ahead and do chromosome
    $query = "SELECT chromosome, UNCOMPRESS(sequence) as unseq FROM $species WHERE chromosome = '$chromosome'";
    $st = $dbh->prepare($query) or die "preparing query failed: $DBI::errstr";
    $st->execute() or die "The execution of query field: $DBI::errstr";
    my $sequence = '';
    while(my $row = $st->fetchrow_hashref()) {
      #see all the genes it is upstream of.
      $sequence = $row->{'unseq'};
    }
    #print ">$chromosome\n$sequence\n";
    $count++;
    #if($count%10000==0) { print "$count\n"; }
    my $start = 1;
    my $finish = length($sequence);
    while($sequence =~ /($regex)/ig) {
      if($1) {
        my $seq = $1;
        my $loc = pos($sequence); 
        my $hitstart = $start + $loc-length($seq);
        my $hitfinish = $start + $loc-1;
        #print "$chromosome\t$hitstart\t$hitfinish\t$seqs[$i]\t$start\t$finish\t$hits\t$singleEvalue\t$blockEvalue\t" . sprintf("%.4f",$gc_content) . "\n";
        my $hitmiddle = int (($hitfinish+$hitstart)/2);
        $zquery = "INSERT INTO hits (chromosome_scan_id,hit_start,hit_finish,hit_middle,sequence) VALUES ($chromosome_scan_id, $hitstart, $hitfinish, $hitmiddle, '$seq')";
        $zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
        $zst->execute() or die "The execution of query field: $DBI::errstr";
      }
    }
    # mark that we did the chromosome
    $zquery = "UPDATE chromosome_scan SET completed = 1, finish_time = CURRENT_TIMESTAMP WHERE db_id = $chromosome_scan_id";
    $zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
    print "Setting chromosome scan entry complete\n$zquery\n";
    $zst->execute() or die "The execution of query field: $DBI::errstr";    
  } else { 
    # skip to here if we already did the chromsome
    print "we already did that chromosome\n";
  }
}
$zquery = "UPDATE regex_scan SET completed = 1, finish_time = CURRENT_TIMESTAMP WHERE db_id = $regex_id";
$zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
print "Setting regular expression scan entry complete\n$zquery\n";
$zst->execute() or die "The execution of query field: $DBI::errstr";    
print "finished_locating_matches\n";
